﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL.code
{
    ///

    /// Summary description for DataProvider
    ///

    public class DataProvider : DataTable
    {
        public DataProvider()
        {
        }
        /*
        * Declare member variable
        */
        private SqlDataAdapter mDataAdapter;
        private string mStringSQL;
        private string mTaName;
        private static SqlConnection mConn;

        /*
        * Declare property
        */
        public static string StringDB;
        public string StringSQL
        {
            get { return mStringSQL; }
            set { mStringSQL = value; }
        }
        public string TaName
        {

            get { return mTaName; }
            set { mTaName = value; }
        }
        public static SqlConnection Conn
        {

            get { return mConn; }
            set { mConn = value; }
        }
        public long RowsCount
        {
            /*
            * count rows in table
            */
            get { return this.Rows.Count; }
        }

        public void GetData(string pTaName, string pStringSQL)
        {
            mTaName = pTaName;
            mStringSQL = pStringSQL;
            ReadTable();
        }

        private DataTable ReadTable()
        {
            if (mConn == null)
            {
                mConn = new SqlConnection();
            }
            mConn.ConnectionString = StringDB;
            try
            {
                mDataAdapter = new SqlDataAdapter(mStringSQL, mConn);
                mDataAdapter.FillSchema(this, SchemaType.Mapped);
                mDataAdapter.Fill(this);

                SqlCommandBuilder CommandBD = new SqlCommandBuilder(mDataAdapter);
                return this;
            }
            catch (SqlException ex)
            {
                return null;
            }
        }

        public DataTable GetDataFromStore(string pStoreName, string[] ParamName, object[] ParamValue)
        {
            if (mConn == null)
            {
                mConn = new SqlConnection();
            }
            mConn.ConnectionString = StringDB;
            try
            {
                SqlCommand cmd = new SqlCommand(pStoreName, mConn);
                if (ParamValue != null)
                    for (int i = 0; i < ParamName.Length; i++)
                        cmd.Parameters.AddWithValue(ParamName[i], ParamValue[i]);
                cmd.CommandType = CommandType.StoredProcedure;

                mDataAdapter = new SqlDataAdapter(cmd);
                mDataAdapter.Fill(this);

                SqlCommandBuilder CommandBD = new SqlCommandBuilder(mDataAdapter);
                CommandBD.Dispose();
                cmd.Dispose();
                return this;
            }
            catch (SqlException ex)
            {
                return null;
            }
        }
        public bool Ghi()
        {
            bool result = true;
            try
            {
                mDataAdapter.Update(this);
                this.AcceptChanges();
            }
            catch (Exception e)
            {
                this.RejectChanges();
                result = false;
            }
            return result;
        }

        /*
        * Filter data from table
        */
        public DataView RecFilter(int startRec, int maxRec)
        {
            DataSet ds = new DataSet();
            mDataAdapter.Fill(ds, startRec, maxRec, "Ket_Qua");
            return ds.Tables["Ket_Qua"].DefaultView;
        }

        private void mDataAdapter_RowUpdated(object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e)
        {
            if (this.PrimaryKey[0].AutoIncrement)
            {
                this.Columns[0].ReadOnly = false;
                /*
                * Write data in autoincrement column
                */
                if (e.Status == UpdateStatus.Continue & e.StatementType == StatementType.Insert)
                {
                    /*
                    * Get value AutoNumber
                    */
                    SqlCommand cmd = new SqlCommand("Select @@IDENTITY", mConn);

                    e.Row.ItemArray[0] = cmd.ExecuteScalar();
                    e.Row.AcceptChanges();
                }
            }
        }
    }
}
